package com.andersen.database.dao;

import org.springframework.stereotype.Component;
import com.andersen.database.entity.ComponentEntity;
import com.andersen.database.entity.StateEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: Avseenko Dmitrij
 * Date: 27.02.14
 * Time: 11:38
 * To change this template use File | Settings | File Templates.
 */

@Component
public class ComponentDao extends AbstractDao{

    private ComponentEntity mapResultSetRow(ResultSet rSet) throws SQLException {
         ComponentEntity componentEntity = new ComponentEntity();

         componentEntity.setId(rSet.getLong("id"));
         componentEntity.setTypeId(rSet.getLong("type_id"));
         componentEntity.setDescription(rSet.getString("description"));
         componentEntity.setByDate(rSet.getDate("buy_date"));
         componentEntity.setWarrantyEndDate(rSet.getDate("warranty_end_date"));
         componentEntity.setSerial(rSet.getString("serial"));
         componentEntity.setDeleted(rSet.getBoolean("deleted"));
         
         return componentEntity;
    }
    
    public ComponentEntity getComponentById(long id) {
        
        LOG.info("getComponentById: " + id);
        
        ComponentEntity entity = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("SELECT c.id, ");
        sql.append("c.type_id, ");
        sql.append("c.description, ");
        sql.append("c.buy_date, ");
        sql.append("c.warranty_end_date, ");
        sql.append("c.serial, ");
        sql.append("c.deleted ");
        sql.append("FROM component c WHERE c.id = ?;");
                             
        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            if(resultSet.first())
                entity = mapResultSetRow(resultSet);
        } catch(SQLException exc) {
            LOG.error("Error getComponentById", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
        return entity;
    }
    
    public List<ComponentEntity> getComponentsForTypeIds(List<Long> ids){

        LOG.info("getComponentsForTypeIds. ids count - " + ids.size());

        ArrayList<ComponentEntity> componentArrayList = new ArrayList<ComponentEntity>();

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();
        
        sql.append("SELECT c.id, ");
            sql.append("c.type_id, ");
            sql.append("c.description, ");
            sql.append("c.buy_date, ");
            sql.append("c.warranty_end_date, ");
            sql.append("c.serial, ");
            sql.append("c.deleted ");
        sql.append("FROM component c ");
        sql.append("WHERE c.deleted = 0 AND c.type_id IN (");
        
        for(int i=0;i<ids.size();i++) {
                sql.append(ids.get(i).toString());
                if (i != (ids.size() - 1))
                    sql.append(", ");
            }
        sql.append(");");
        
        
        /*sql.append("SELECT component.id, ");
            sql.append("component.type_id, ");
            sql.append("component.description, ");
            sql.append("component.buy_date, ");
            sql.append("component.warranty_end_date, ");
            sql.append("component.serial, ");
            sql.append("component.deleted ");
        sql.append("FROM component ");
            sql.append("JOIN (");
                sql.append("SELECT component_type2.id AS id, ");
                    sql.append("component_type.name AS nameFather, ");
                    sql.append("component_type2.name AS nameSon, ");
                    sql.append("component_type2.parent_type_id AS parent_type_id ");
                sql.append("FROM component_type JOIN (");
                    sql.append("SELECT id, ");
                        sql.append("parent_type_id, ");
                        sql.append("name ");
                    sql.append("FROM equipment.component_type) ");
                sql.append("component_type2 ");
                sql.append("ON component_type.id = ? ");
                sql.append("AND component_type.id = component_type2.parent_type_id) ");
                sql.append("components ON components.id = component.type_id;");*/
        
        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            //statement.setLong(1, id);
            resultSet = statement.executeQuery();
            if(resultSet.first()){
                do{
                   ComponentEntity componentEntity = mapResultSetRow(resultSet);
                   componentArrayList.add(componentEntity);
                }while(resultSet.next());
            }

        }catch(SQLException exc) {
            LOG.error("Error getComponentsForTypeId", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

        return componentArrayList;
    }

    /*public boolean deleteComponentForId(long id){

        boolean returnBool = true;

        LOG.info("deleteComponentForId: " + id);

        Connection connection = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;

        String request =    "DELETE" +
                            " FROM" +
                            "  equipment.component" +
                            "WHERE" +
                            "  `id` = ?;";

        try{

            connection = dataSource.getConnection();
            statment = connection.prepareStatement(request);
            statment.setLong(1, id);
            statment.executeQuery();

        }catch(SQLException exc) {
            LOG.error("Error deleteComponentForId", exc);
            returnBool = false;
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statment != null) statment.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {}
        }

        return returnBool;
    }*/

    public boolean updateComponent(ComponentEntity componentEntity){

        boolean returnBool = false;

        LOG.info("ComponentDao.updateComponent() - id = " + componentEntity.getId());

        Connection connection = null;
        PreparedStatement statement = null;

        StringBuilder sql = new StringBuilder();
        
        sql.append("UPDATE equipment.component ");
        sql.append("SET ");
        sql.append("description = ?, ");
        sql.append("buy_date = ?, ");
        sql.append("warranty_end_date = ?, ");
        sql.append("serial = ?, ");
        sql.append("deleted = ? ");
        sql.append("WHERE id = ?;");
        
        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, componentEntity.getDescription());
            statement.setDate(2, componentEntity.getByDate());
            statement.setDate(3, componentEntity.getWarrantyEndDate());
            statement.setString(4, componentEntity.getSerial());
            statement.setBoolean(5, componentEntity.getDeleted());
            statement.setLong(6, componentEntity.getId());

            returnBool = (statement.executeUpdate() > 0);

        }catch(SQLException exc) {
            LOG.error("Error ComponentDao.updateComponent()", exc);
            returnBool = false;
        } finally {
                safeClose(statement);
                safeClose(connection);
        }
        return returnBool;
    }

    public long[] addComponent(ComponentEntity componentEntity, StateEntity firstState) {

        long[] returnKeys = {ID_INVALID,ID_INVALID};

        LOG.info("ComponentDao.addComponent()");

        Connection connection = null;
        PreparedStatement statement = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        ResultSet resultSet2 = null;

        StringBuilder compSql = new StringBuilder();
        
        compSql.append("INSERT INTO component (");
            compSql.append("type_id, ");
            compSql.append("description, ");
            compSql.append("buy_date, ");
            compSql.append("warranty_end_date, ");
            compSql.append("serial) ");
        compSql.append("VALUE(?,?,?,?,?);");
        
        StringBuilder stateSql = new StringBuilder();
        
        stateSql.append("INSERT INTO state(");
            stateSql.append("component_id, ");
            stateSql.append("from_date, ");
            stateSql.append("employee_id, ");
            stateSql.append("computer_id, ");
            stateSql.append("office_id, ");
            stateSql.append("status) ");
        stateSql.append("VALUES(?,?,?,?,?,?);");
        
        try{
            LOG.info("Begin tran");
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(compSql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);
            statement.setLong(1, componentEntity.getTypeId());
            statement.setString(2, componentEntity.getDescription());
            statement.setDate(3, componentEntity.getByDate());
            statement.setDate(4, componentEntity.getWarrantyEndDate());
            statement.setString(5, componentEntity.getSerial());
            if (statement.executeUpdate()> 0) {
                resultSet = statement.getGeneratedKeys();
                resultSet.first();
                returnKeys[0] = resultSet.getLong(1);
                
                LOG.info("ComponentDao.addComponent() - Component req finished. First key - " + returnKeys[0]);
                
                statement2 = connection.prepareStatement(stateSql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);
                
                statement2.setLong(1, returnKeys[0]);
                statement2.setDate(2, firstState.getFromDate());
                if (firstState.getEmployeeId() != ID_NOT_DEFINED) statement2.setLong(3, firstState.getEmployeeId());
                    else statement2.setNull(3, java.sql.Types.NULL);
                if (firstState.getComputerId() != ID_NOT_DEFINED) statement2.setLong(4, firstState.getComputerId());
                    else statement2.setNull(4, java.sql.Types.NULL);
                statement2.setLong(5, firstState.getOfficeId());
                if (firstState.getStatus() != StateEntity.STATUS_OK) statement2.setString(6, String.valueOf(firstState.getStatus()));
                    else statement2.setNull(6, java.sql.Types.NULL);
                
                if (statement2.executeUpdate() > 0) {
                    resultSet2 = statement2.getGeneratedKeys();
                    resultSet2.first();
                    returnKeys[1] = resultSet2.getLong(1);
                    LOG.info("ComponentDao.addComponent() - State req finished. Second key - " + returnKeys[1]);
                }
            }
            if (returnKeys[0] > 0 && returnKeys[1] > 0) connection.commit();
        }catch(SQLException exc) {
            LOG.error("Error ComponentDao.addComponent()", exc);
            returnKeys[0] = returnKeys[1] = ID_INVALID;
            try {
                if (connection != null) connection.rollback();
            } catch (SQLException ex) {
                LOG.error("Error ComponentDao.addComponent() - cannot rollback tran");
            }
        } finally {
            safeClose(resultSet2);
            safeClose(resultSet);
            safeClose(statement2);
            safeClose(statement);
            safeClose(connection);
        }
        return returnKeys;
    }
    
    public ArrayList<ComponentEntity> getConnectedComponents(long computerId) {
        
        ArrayList<ComponentEntity> components = new ArrayList<ComponentEntity>();
        LOG.info("ComponentDao.getConnectedComponents()");

        Connection connection = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("SELECT c.id, ");
            sql.append("c.type_id, ");
            sql.append("c.description, ");
            sql.append("c.buy_date, ");
            sql.append("c.warranty_end_date, ");
            sql.append("c.serial, ");
            sql.append("c.deleted ");
        sql.append("FROM component c JOIN state s ON c.id = s.component_id ");
        sql.append("WHERE s.to_date IS NULL AND s.computer_id = ?;");
        
        try {
            connection = dataSource.getConnection();
            statment = connection.prepareStatement(sql.toString());
            statment.setLong(1, computerId);
            resultSet = statment.executeQuery();
            while (resultSet.next()) {
                ComponentEntity component = mapResultSetRow(resultSet);
                components.add(component);
            }
        } catch(SQLException exc) {
            LOG.error("Error ComponentDao.getConnectedComponents", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statment);
            safeClose(connection);
        }
        return components;
    }
    
    public ArrayList<ComponentEntity> getComponentsOfEmployee(long employeeId, boolean withSubcomponents) {
        
        ArrayList<ComponentEntity> components = new ArrayList<ComponentEntity>();
        LOG.info("ComponentDao.getComponentsOfEmployee() - id = " + employeeId);

        Connection connection = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("SELECT c.id AS id, ");
            sql.append("c.type_id AS type_id, ");
            sql.append("c.description AS description, ");
            sql.append("c.buy_date AS buy_date, ");
            sql.append("c.warranty_end_date AS warranty_end_date, ");
            sql.append("c.serial AS serial, ");
            sql.append("c.deleted AS deleted ");
        sql.append("FROM component c JOIN state s ON c.id = s.component_id");
        sql.append("WHERE s.to_date IS NULL AND s.employee_id = ?");
        
        if (withSubcomponents) {
            sql.append(" UNION SELECT DISTINCT c1.id AS id, ");
                sql.append("c1.type_id AS type_id, ");
                sql.append("c1.description AS description, ");
                sql.append("c1.buy_date AS buy_date, ");
                sql.append("c1.warranty_end_date AS warranty_end_date, ");
                sql.append("c1.serial AS serial, ");
                sql.append("c1.deleted AS deleted ");
            sql.append("FROM component c1 JOIN state s1 ON c1.id = s1.component_id ");
            sql.append("JOIN state s2 ON s1.computer_id = s2.component_id ");
            sql.append("WHERE s1.to_date IS NULL AND s2.employee_id = ? AND s2.to_date IS NULL");
        }
        
        sql.append(";");
        
        try {
            connection = dataSource.getConnection();
            statment = connection.prepareStatement(sql.toString());
            statment.setLong(1, employeeId);
            if (withSubcomponents) statment.setLong(2, employeeId);
            resultSet = statment.executeQuery();
            while (resultSet.next()) {
                ComponentEntity component = mapResultSetRow(resultSet);
                components.add(component);
            }
        } catch(SQLException exc) {
            LOG.error("Error ComponentDao.getComponentsOfEmployee()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statment);
            safeClose(connection);
        }
        return components;
    }

    
}
